

	
	**This .do file cleans ICE SC Removals Data, FY2009 to end of 2015**
	
		

			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**


***
**import raw ICE data, fy15 plus nov - dec
***

insheet using ///
"sc removals 2015 plus novdec .csv", ///
names clear

**delete first five lines
keep if _n > 5

**make first row variable names
foreach var of varlist * {
 capture rename `var' `=strtoname(`var'[1])'
}

*delete first row
drop if _n==1

***
*Fix Dates
***

rename Latest_Apprehension_Date Arrest_Date

foreach var of varlist Arrest_Date Detainer_Prepare_Date ///
Final_Order_Date Departed_Date Entry_Date MSCC_Conviction_Date{

gen temp = date(`var', "MDY", 2030)
format temp %td
drop `var'
rename temp `var'

}


**
*Remove redacted info
**

drop Case_Id Subject_Id


* make lowercase
*------------------------------
renvars, lower

**check time period -- three errors we can drop
count if departed_d < d(01oct2014) | departed_d > d(31dec2015) 
assert r(N) == 3
drop if departed_d < d(01oct2014) | departed_d > d(31dec2015) 

**how many obs total
count
assert r(N) == 73952

**save

compress

save "sc removals 2015 plus novdec.dta", replace


*-------------------------------------------------------------------------------
* open 2009-2013 removals data
*-------------------------------------------------------------------------------

insheet using ///
 "Highlighted_FOIA_2015-ICAP-00121 removals.csv", ///
 names clear
 
**delete first five lines
keep if _n > 5

**make first row variable names
foreach var of varlist * {
 capture rename `var' `=strtoname(`var'[1])'
}

**drop excess variables
drop v50-v63

**drop first row
drop if _n == 1


* save
*------------------------------
save "sc removals 9-13", replace

*-------------------------------------------------------------------------------
* open 2014 removals data
*-------------------------------------------------------------------------------

insheet using ///
"FOIA 15-00568_TRAC SC Removals FY14 through Nov 2014_LESA-STU_FINAL(Rev)_FinalCopy - fy14 removals.csv", ///
names clear

**delete first five lines
keep if _n > 5

**make first row variable names
foreach var of varlist * {
 capture rename `var' `=strtoname(`var'[1])'
}

**drop excess variables
drop v50-v67

**drop first row
drop if _n == 1

* append 2009-2013 data
*------------------------------

append using "sc removals 9-13"

***
*Fix Dates
***

rename Latest_Apprehension_Date Arrest_Date


foreach var of varlist Arrest_Date Detainer_Prepare_Date ///
Final_Order_Date Departed_Date Entry_Date MSCC_Conviction_Date{

gen temp = date(`var', "MDY", 2030)
format temp %td
drop `var'
rename temp `var'

}


* drop oct-nov 2014, since that's in 2015-2017 data
*------------------------------

drop if Departed_D >= d(01oct2014)

renvars, lower

* append 2015 data
*------------------------------

append using "sc removals 2015 plus novdec.dta"


* create FY variable
*------------------------------

gen fy = 2009 if departed_d >= d(01oct2008) & departed_d <= d(30sep2009)

forval i = 2009/2015 {
local y = `i' + 1
replace fy = `y' if departed_d >= d(01oct`i') & departed_d <= d(30sep`y')
}

*Check number of observations
count
assert r(N) == 454474


**
*create month variable by arrest month
**

gen month = mofd(departed_date)
format month %tm
assert month < .

**
*Clean county variable
**


*make lower case
replace county = lower(county)

*remove borough and city from county names
replace county = subinstr(county," borough", "", .)
replace county = subinstr(county," city", "", .)

**
*Create state name
**

statastates, ab(state)

replace state = state_name if state_n != ""
replace state = lower(state)
replace state = "guam" if state == "gm"
replace state = "puerto rico" if state == "pr"
replace state = "virgin islands" if state == "vi"

drop state_name state_fips _merge

*fix county names
replace county = "st. joseph" if state == "indiana" & county == "saint joseph"
replace county = "st. louis" if state == "missouri" & county == "saint louis"
replace county = "st. lucie" if state == "florida" & county == "saint lucie"
replace county = "st. louis city" if county == "saint louis" & state == "missouri"
replace county = "alexandria city" if county == "alexandria"
replace county = "virginia beach city" if county == "virginia beach"
replace county = "anchorage municipality" if county == "anchorage"
replace county = "norfolk city" if county == "norfolk" & state == "virginia"
replace county = "richmond city" if county == "richmond" & state == "virginia"
replace county = "chesapeake city" if county == "chesapeake" & state == "virginia"
replace county = "honolulu" if county == "oahu" & state == "hawaii"

**Notes
*Can collapse Richmond City and County because city is larger and we have sanctuary info on it.
*Only Alexandria City is a county; there is no Alexandria County.
*Only Virginia Beach City is a county; there is no Virginia Beach County.
*Only Norfolk City is a county; there is no Norfolk County.
*Fairfax county is far larger than Fairfax city, and sanctuary info is for county.
*Chesapeake is not part of final sample.

*-------------------------------------------------------------------------------
* combine NYC counties
*-------------------------------------------------------------------------------

replace county = "new york" if state == "new york" & county == "kings"
replace county = "new york" if state == "new york" & county == "bronx"
replace county = "new york" if state == "new york" & county == "richmond"
replace county = "new york" if state == "new york" & county == "queens"


**
*Save
**

compress
save "scremovals9-15.dta", replace

erase "sc removals 2015 plus novdec.dta"
erase "sc removals 9-13.dta" 






